
# 自定义字段迁移，自定义字段分组迁移
from tools.dbo import carried_sql, put_mysql_data, get_server_data, get_server_data_car, get_dict_data_sql


def syn_cus_field(sql_server_conn, mysql_conn):
    print("===============================正在迁移自定义字段===============================")
    carried_sql(mysql_conn,"TRUNCATE cus_field")
    carried_sql(mysql_conn,"TRUNCATE cus_field_category")
    customFieldType="""
    case 
    when customFieldType < 6 then customFieldType + 1
    when customFieldType = 7 then 8
    when customFieldType = 6 then 8
    when customFieldType > 8 then customFieldType + 2
    else customFieldType end
    """
    # 自定义字段迁移
    return_data = get_server_data(sql_server_conn, "2,id,customFieldNo,customFieldNm,customFieldDesc,customFieldSortId,"+customFieldType+",dataScale,minVal,maxVal,defaultVal,fomartString,isMustFill,0,isDel,1", "1 = 1" , "SV_CustomField")
    print(put_mysql_data(mysql_conn, "tenant_id,id,code,name,remark,category_id,type_id,accuracy,min_value,max_value,default_value,date_format,is_required,is_enable,is_delete,is_only", "cus_field", return_data))
    # UPDATE cus_field SET type_id = type_id + 1 WHERE type_id < 6
    # UPDATE cus_field SET type_id = type_id + 1 WHERE type_id = 7
    # UPDATE cus_field SET is_enable = 0 采用select设定默认值
    # UPDATE cus_field SET type_id = type_id +2 WHERE type_id = 6
    # UPDATE cus_field SET is_only = 1 采用select设定默认值
    # UPDATE cus_field set max_value = NULL,min_value = NULL,accuracy = NULL where type_id != 1
    carried_sql(mysql_conn, "UPDATE cus_field set max_value = NULL,min_value = NULL,accuracy = NULL where type_id != 1")

    select_custom_data_source = "select dataSource,id from SV_CustomField where customFieldType in (6,7,8)"
    return_data = get_server_data_car(sql_server_conn,select_custom_data_source)
    update_project_sample_type = "update cus_field set dictionary_id=%s where id=%s"
    cursor = mysql_conn.cursor()
    cursor.executemany(update_project_sample_type, return_data)
    mysql_conn.commit()
    lines = cursor.rowcount
    print(lines)
    update_custom_dict_id = """
        update cus_field cf left join bims_dict dict 
        on dict.tmp=cf.dictionary_id
        set cf.dictionary_id=dict.id,cf.dictionary_level=dict.floor
        where cf.type_id=8
    """

    carried_sql(mysql_conn,update_custom_dict_id)

    #自定义字段分组迁移
    return_data = get_server_data(sql_server_conn,
                                  "2,id,customFieldSortNo,customFieldSortNm,isDel",
                                  "1 = 1", "SV_CustomFieldSort")
    print(put_mysql_data(mysql_conn,
                         "tenant_id,id,code,name,is_delete",
                         "cus_field_category", return_data))
    print("===============================自定义字段迁移完成===============================")

zzz = -1;
# 字典迁移
def syn_dict(sql_server_conn, mysql_conn):
    # 迁移之前需要手动在数据库表中加入tmp字段，同步完成后删除该字段
    print("===============================正在迁移字典数据===============================")
    carried_sql(mysql_conn,"TRUNCATE bims_dict")
    return_data = get_server_data(sql_server_conn, "2,id,parentId,dictionaryNo,dictionaryNm,parentId,dictionaryLevel,mark,0", "1 = 1" , "SV_Dictionary")
    print(put_mysql_data(mysql_conn, "tenant_id,tmp,root_id,code,label,p_id,floor,remark,status", "bims_dict", return_data))
    update_dict_id_sql = """
    update bims_dict d inner join (
        select id,tmp from bims_dict
    )t on d.p_id = t.tmp
    set d.root_id=t.id,d.p_id=t.id
    """
    carried_sql(mysql_conn,update_dict_id_sql)
    return_data = get_dict_data_sql(mysql_conn, "select id,p_id from bims_dict")
    dept_dict = {};
    for data in return_data:
        dept_dict[data["id"]] = data["p_id"]
    aa = []
    for data in return_data:
        deot_dict_value = {};
        deot_dict_value["id"] = data["id"];
        global zzz
        zzz = 0
        rootid = syn_bims_dict(dept_dict, deot_dict_value["id"])
        deot_dict_value["rootid"] = rootid;
        deot_dict_value["floor"] = zzz;
        aa.append(deot_dict_value)
    for a in aa:
        carried_sql(mysql_conn, "update bims_dict set root_id="+str(a["rootid"])+",floor="+str(a["floor"])+" where id = "+str(a["id"]))
    print("===============================字典迁移完成===============================")

# 查找字典递归函数
def syn_bims_dict(dept_dict,id):
    if dept_dict[id] == 0:
        return id
    global zzz
    zzz = zzz+1
    return syn_bims_dict(dept_dict,dept_dict[id])

# 单位迁移
def sys_unit(sql_server_conn, mysql_conn):
    # 插入一条单位分类数据
    carried_sql(mysql_conn,"TRUNCATE bims_unit")
    print("===============================正在迁移单位数据===============================")
    # insert_unit = "insert into bims_dict(root_id,p_id,label,status,floor) values(0,0,'单位',0,0)"
    # unit_id = carried_sql(mysql_conn,insert_unit)
    return_data = get_server_data(sql_server_conn, "id,unitNo,unitNm,mark,isrtEmpId,isrtDt,updtEmpId,updtDt,2", "isDel = 0" , "AP_Base_Unit")
    print(put_mysql_data(mysql_conn, "id,code,name,remark,create_by,create_time,update_by,update_time,tenant_id", "bims_unit", return_data))
    print("===============================单位数据迁移完成===============================")
